# -*- coding: UTF-8 -*-
#!/usr/bin/python
import json
import xlwt
class AnalysisJson:
	def __init__(self,josnn):
		if josnn.strip()!='':
			j=json.loads(josnn.encode('UTF-8'));
			singleResult=json.dumps(json.loads(j['singleResult']),ensure_ascii=False)
			self.jsons = json.loads(singleResult)
		else:
			self.jsons = None
	def toInsertSql(self,path):
		sql=self.excJsonToSql()
		f = open(path, 'w',encoding='utf8')
		try:
			if sql.strip()!='':
				f.write(sql)
			else:
				print('sql数据为空')
				return -1
		finally:
			f.close()

	def toExcel(self,path):
		if self.jsons is None:
			print('json 解析失败')
			return ''
		resultsRoots=self.jsons['resultsRoot']
		results=resultsRoots[0]['results']
		resultColumns=resultsRoots[1]['resultColumns']
		book = xlwt.Workbook() # 创建一个excel对象
		sheet = book.add_sheet('Sheet1',cell_overwrite_ok=True) # 添加一个sheet页
		i=0
		for resultColumn in resultColumns:
			column=resultColumn['dataIndex']
			sheet.write(0,i,column) # 将title数组中的字段写入到0行i列中
			i=i+1
		i=1
		j=0
		for result in results:
			j=0
			for resultColumn in resultColumns:
				column=resultColumn['dataIndex']
				sheet.write(i,j,result[column])
				j=j+1
			i=i+1
		book.save(path)
	def excJsonToSql(self):
		if self.jsons is None:
			print('json 解析失败')
			return ''
		resultsRoots=self.jsons['resultsRoot']
		hiveSql=self.jsons['hiveSql']
		print(hiveSql)
		fromn=hiveSql.find('from')
		if fromn<0:
			fromn=hiveSql.find('FROM')
		wheren=hiveSql.find('where')
		if wheren<0:
			wheren=hiveSql.find('WHERE')
		if wheren<0:
			wheren=hiveSql.find('limit')
		if wheren<0:
			wheren=hiveSql.find('LIMIT')
		tablename=hiveSql[fromn+4:wheren]
		#print(tablename)
		results=resultsRoots[0]['results']
		resultColumns=resultsRoots[1]['resultColumns']
		sql='insert into '+tablename+'('
		for resultColumn in resultColumns:
			sql=sql+resultColumn['dataIndex']+','
		sql=sql[0:len(sql)-1]
		sql=sql+') values'
		#print(sql)
		for result in results:
			sql=sql+'('
			for resultColumn in resultColumns:
				column=resultColumn['dataIndex']
				f=result[column]
				if f is None:
					sql=sql+'null,'
				else:
					sql=sql+'\''+f+'\','
			sql=sql[0:len(sql)-1]
			sql=sql+'),\n'
		return sql[0:len(sql)-2]
		#print(sql)
 
#print(type(resultsRoots[0]['results']))
#print(json.dumps(results))